﻿/******************************************************    
文件名:TablePdfDB.cs
版权  (c)  2010-11-23 淘渺圈圈工作室
作者:黄媛媛
创建日期:2010-12-03
维护人员:黄媛媛
维护日期:2010-12-03
文件功能描述:金属材料热处理网络学习平台_访问pdf数据表操作
******************************************************/
using System;
using System.Collections.Generic;
using System.Text;
using System.Data.SqlClient;
using System.Configuration;
using System.Collections;
using System.Data;

namespace Mmht.DB
{
    public class TablePdfDB
    {
        operate op = new operate();

        public DataTable GetPdfUrl(string type)
        {
            string sql = "select id,name_cn,image_url,pdf_url,num_pages from pdf where type='" + type + "' and pdf_url is not null ";
            DataTable dt = new DataTable();
            dt = op.Query(sql);
            return dt;
        }

        public bool SavePdfUrl(DataTable dt)
        {
            bool save = false;
            string pdf_url = string.Empty;
            string image_url = string.Empty;
            string sql = string.Empty;
            if (dt == null || dt.Rows.Count == 0)
            {
                save = false;
            }
            else
            {
                for (int i = 0; i < dt.Rows.Count; i++)
                {
                    pdf_url = dt.Rows[i]["pdf_url"].ToString();
                    image_url = dt.Rows[i]["image_url"].ToString();
                    sql = "update pdf set image_url='" + image_url + "' where pdf_url='" + pdf_url + "'";
                    save = op.Execute(sql);
                }
            }
            return save;
        }

        /// <summary>
        /// 
        /// </summary>
        /// <param name="id"></param>
        /// <returns></returns>
        public DataRow GetPdfById(int id)
        {
            string sql = "select * from pdf where id = " + id.ToString();
            DataTable dt = new DataTable();
            dt = op.Query(sql);
            if (dt.Rows.Count > 0)
            {
                return dt.Rows[0];
            }
            return null;
        }

        /// <summary>
        /// 选出点击率最高的top个pdf信息
        /// </summary>
        /// <param name="top">个数</param>
        /// <returns></returns>
        public DataTable GetCTRTopPdf(int top)
        {
            string sql = @"select top "+top.ToString()
                        + " * from pdf order by viewnum desc";
            return op.Query(sql);
        }

        /// <summary>
        /// 选出点击率最高且被推荐的top个pdf信息
        /// </summary>
        /// <param name="top">个数</param>
        /// <returns></returns>
        public DataTable GetIsCommendTopPdf(int top)
        {
            string sql = @"select top "+top.ToString()
                        +" * from pdf where iscommend =1 order by viewnum desc";
            return op.Query(sql);
        }

        public void addPdfViewNum(int pdfId)
        {
            string sql = @"update pdf set viewnum = viewnum + 1 where id = " + pdfId.ToString() + " ";
            bool s = op.Execute(sql);
        }
        public DataTable SelectPDF(string type) 
        {
            DataTable dt = new DataTable();
            string sql = string.Empty;
            sql = "select * from pdf where type ='"+type .ToString ()+"'";
            dt = op.Query(sql);
            return dt;
        }
        public DataTable SelectAllPDF()
        {
            DataTable dt = new DataTable();
            string sql = string.Empty;
            sql = "select * from pdf order by type desc";
            dt = op.Query(sql);
            return dt;
        }
        public bool DelPDF(string type,int id) 
        {
            string sql = string.Empty;
            sql = "delete from pdf where type='"+type .ToString ()+"' and id="+id.ToString ()+"";
            bool bl = op.Execute(sql);
            return bl;
        }
        public DataTable GetPdfByName(string name,string type) 
        {
            DataTable dt = new DataTable();
            string sql = string.Empty;
            sql = "select * from pdf where name_cn='"+name.ToString ()+"' and type='"+type .ToString ()+"'";
            dt = op.Query(sql);
            return dt;
        }
        public bool UpdatePDF(string name,string author,string remark,int id)
        {
            string sql = string.Empty;
            sql = "update pdf set name_cn='"+name.ToString ()+"',author='"+author .ToString ()+"',remark='"+remark .ToString ()+"' where id="+id.ToString ()+"";
            bool bl = op.Execute(sql);
            return bl;
        }
        public bool UpdatePDFbz(string name_cn,string name_en,string code,string remark,int id) 
        {
            string sql = string.Empty;
            sql = "update pdf set name_cn='"+name_cn .ToString ()+"',name_en='"+name_en .ToString ()+"',code='"+code .ToString ()+"',remark='"+remark .ToString ()+"' where id ="+id.ToString ()+"";
            bool bl = op.Execute(sql);
            return bl;
        }


        public DataTable GetPdfByName(string name)
        {
            throw new Exception("The method or operation is not implemented.");
        }        
        public bool Recommend(int id) 
        {
            string sql = string.Empty;
            sql = "update pdf set iscommend = 'true' where id ="+id.ToString ()+"";
            bool bl = op.Execute(sql);
            return bl;
        }
        public bool Unrecommend(int id) 
        {
            string sql = string.Empty;
            sql = "update pdf set iscommend = 'false' where id =" + id.ToString() + "";
            bool bl = op.Execute(sql);
            return bl;
        }
        public bool ClearViewnum(int id) 
        {
            string sql = string.Empty;
            sql = "update pdf set viewnum = 0 where id ="+id.ToString ()+"";
            bool bl = op.Execute(sql);
            return bl;
        }
        public bool ClearAllViewnum() 
        {
            string sql = string.Empty;
            sql = "update pdf set viewnum = 0";
            bool bl = op.Execute(sql);
            return bl;
        }

        public void UploadPdf(DataTable dt, string type, int create_by, DateTime create_at)
        {
            string name_cn = string.Empty;
            string url=string.Empty;//files/pdf/bz/GB_1221-1992耐热钢棒.pdf
            string sql = string.Empty;
            for (int i = 0; i < dt.Rows.Count; i++)
            {
                name_cn = dt.Rows[i][0].ToString();
                url=dt.Rows[i][1].ToString();
                DelPdf(name_cn, type);
                sql = @"INSERT INTO pdf
                    (name_cn,type,created_at,created_by,pdf_url,viewnum,iscommend)
                     VALUES
                    ('" + name_cn + "','" + type + "','" + create_at + "','" + create_by.ToString() + "','" + url + "',0,'false')";
                op.Execute(sql);
            }
        }

        public bool DelPdf(string name_cn, string type)
        {
            string sql = string.Empty;
            sql = "delete from pdf where type='" + type + "' and name_cn='" + name_cn + "'";
            bool bl = op.Execute(sql);
            return bl;
        }

        public DataTable UploadPdfInfo(DateTime create_at, int create_by)
        {
            string sql = string.Empty;
//            sql = @"SELECT name_cn as 文件名,type as 文件类型,a.created_at as 上传时间,name as 用户名,pdf_url as 文件路径,viewnum as 阅览次数
//                    FROM pdf a,admin b
//                    WHERE a.created_by=b.id and a.created_at > '2010-12-29 19:32:53' ";
            sql = @"SELECT name_cn as 文件名,type as 文件类型,a.created_at as 上传时间,name as 用户名,pdf_url as 文件路径,viewnum as 阅览次数
                    FROM pdf a,admin b
                    WHERE a.created_by=b.id and a.created_at = '" + create_at.ToString("yyyy-MM-dd HH:mm:ss") +"' ";
            return op.Query(sql);
        }

        public DataTable SearchPdf(string key)
        {
            DataTable dt = new DataTable();
            string sql = string.Empty;
            sql = "select * from pdf where name_cn like '%" + key + "%' order by created_at desc";
            dt = op.Query(sql);
            return dt;
        }
    }
}
